<?php

/**
 * This is the model class for table "products".
 *
 * The followings are the available columns in table 'products':
 * @property integer $id
 * @property integer $firmid
 * @property string $name
 * @property string $description
 * @property integer $producttype
 * @property string $created
 * @property string $price
 * @property integer $isactive
 * @property string $billprice
 *
 * The followings are the available model relations:
 * @property Prodtypes $producttype0
 * @property Firms $firm
 * @property Store[] $stores
 *
 * The calculated fields
 * @property integer $unsoldcount
 */
class Products extends CActiveRecord
{
	public $unsoldcount;
	public $bought;
	public $sumbought;
	public $sold;
	public $sumsold;
	
	/**
	 * Returns the static model of the specified AR class.
	 * @return Products the static model class
	 */
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}

	/**
	 * @return string the associated database table name
	 */
	public function tableName()
	{
		return 'products';
	}

	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			array('firmid', 'required'),
			array('firmid, producttype, isactive, unsoldcount', 'numerical', 'integerOnly'=>true),
			array('price,billprice', 'length', 'max'=>10),
			array('name, description', 'safe'),
			// The following rule is used by search().
			// Please remove those attributes that should not be searched.
			array('id, firmid, name, description, producttype, created, price, billprice, isactive, unsoldcount, bought, sumbought', 'safe', 'on'=>'search'),
		);
	}

	/**
	 * @return array relational rules.
	 */
	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
			'producttype0' => array(self::BELONGS_TO, 'Prodtypes', 'producttype'),
			'firm' => array(self::BELONGS_TO, 'Firms', 'firmid'),
			'stores' => array(self::HAS_MANY, 'Store', 'productid'),
		);
	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
		return array(
			'id' => 'ID',
			'firmid' => 'Firmid',
			'name' => 'Name',
			'description' => 'Description',
			'producttype' => 'Producttype',
			'created' => 'Created',
			'price' => 'Price',
			'billprice' => 'Billprice',
			'isactive' => 'Isactive',
			'unsoldcount' => 'Unsold',
		);
	}

	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
	 */
	public function search()
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.

		$criteria=new CDbCriteria(array('with'=>'stores'));

		$criteria->select=array("*","(SELECT COUNT(*) FROM store WHERE productid=t.id AND store.pricesell IS NULL AND store.datesell IS NULL) AS unsoldcount");
		$criteria->compare('id',$this->id);
		if(isset($this->firmid)&&$this->firmid<=0) $this->firmid="";
		$criteria->compare('firmid',$this->firmid);
		$criteria->compare('name',$this->name,true);
		$criteria->compare('description',$this->description,true);
		$criteria->compare('producttype',$this->producttype);
		$criteria->compare('created',$this->created,true);
		$criteria->compare('price',$this->price,true);
		$criteria->compare('billprice',$this->billprice,true);
		$criteria->compare('isactive',$this->isactive);
		if($this->unsoldcount) 
		{
			$compareOperator="=";
			if(strpos($this->unsoldcount,">")===0 || strpos($this->unsoldcount,"<")===0) $compareOperator="";
			$criteria->addCondition('(SELECT COUNT(*) FROM store WHERE productid=t.id AND store.pricesell IS NULL AND store.datesell IS NULL)'.$compareOperator.$this->unsoldcount);
		}
		return new CActiveDataProvider(get_class($this), array(
			'criteria'=>$criteria,
		));
	}
	
	public function getTotals($provider)
	{
		$totals = array();
		$sum = 0;
		$count = 0;
		$sold = 0;
		$sumsold = 0;
		$sumprice = 0;
		$sumbillprice = 0;
		$sumcount = 0;
		$data = $provider;//$provider->getData();
		foreach($data as $p)
		{	
			$sum+=$p->sumbought;
			$count+=$p->bought;
			$sold+=$p->sold;
			$sumsold+=$p->sumsold;
			$sumprice+=$p->price * $p->unsoldcount;
			$sumbillprice+=$p->billprice * $p->unsoldcount;
			$sumcount+=$p->unsoldcount;
		}
		$totals['sum']=$sum;
		$totals['count']=$count;
		$totals['sumsold']=$sumsold;
		$totals['sold']=$sold;
		$totals['sumprice']=$sumprice;
		$totals['sumbillprice']=$sumbillprice;
		$totals['sumcount']=$sumcount;
		return $totals;
	}

	public function advanced_search($start_date,$end_date,$returnprovider=true)
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.

		$criteria=new CDbCriteria(array('with'=>'stores'));

		$criteria->select=array("*",
		"(SELECT COUNT(*) FROM store WHERE productid=t.id AND store.pricesell IS NULL AND store.datesell IS NULL) AS unsoldcount",
		"(SELECT COUNT(*) FROM store WHERE productid=t.id AND store.pricebuy IS NOT NULL AND store.datebuy BETWEEN '".$start_date."' AND '".$end_date."')  AS bought",
		"(SELECT SUM(pricebuy) FROM store WHERE productid=t.id AND store.pricebuy IS NOT NULL AND store.datebuy BETWEEN '".$start_date."' AND '".$end_date."')  AS sumbought",
		"(SELECT COUNT(*) FROM store WHERE productid=t.id AND store.pricesell IS NOT NULL AND store.datesell BETWEEN '".$start_date."' AND '".$end_date."')  AS sold",
		"(SELECT SUM(pricesell) FROM store WHERE productid=t.id AND store.pricesell IS NOT NULL AND store.datesell BETWEEN '".$start_date."' AND '".$end_date."')  AS sumsold");
		$criteria->compare('id',$this->id);
		if(isset($this->firmid)&&$this->firmid<=0) $this->firmid="";
		$criteria->compare('firmid',$this->firmid);
		$criteria->compare('name',$this->name,true);
		$criteria->compare('description',$this->description,true);
		$criteria->compare('producttype',$this->producttype);
		$criteria->compare('created',$this->created,true);
		$criteria->compare('price',$this->price,true);
		$criteria->compare('billprice',$this->billprice,true);
		$criteria->compare('isactive',$this->isactive);
		if($this->unsoldcount) 
		{
			$compareOperator="=";
			if(strpos($this->unsoldcount,">")===0 || strpos($this->unsoldcount,"<")===0) $compareOperator="";
			$criteria->addCondition('(SELECT COUNT(*) FROM store WHERE productid=t.id AND store.pricesell IS NULL AND store.datesell IS NULL)'.$compareOperator.$this->unsoldcount);
		}
		if($this->bought) 
		{
			$compareOperator="=";
			if(strpos($this->bought,">")===0 || strpos($this->bought,"<")===0) $compareOperator="";
			$criteria->addCondition("(SELECT COUNT(*) FROM store WHERE productid=t.id AND store.pricebuy IS NOT NULL AND store.datebuy BETWEEN '".$start_date."' AND '".$end_date."')".$compareOperator.$this->bought);
		}
		if($this->sumbought) 
		{
			$compareOperator="=";
			if(strpos($this->sumbought,">")===0 || strpos($this->sumbought,"<")===0) $compareOperator="";
			$criteria->addCondition("(SELECT SUM(pricebuy) FROM store WHERE productid=t.id AND store.pricebuy IS NOT NULL AND store.datebuy BETWEEN '".$start_date."' AND '".$end_date."')".$compareOperator.$this->sumbought);
		}
		
		if($returnprovider){
		return new CActiveDataProvider(get_class($this), array(
			'criteria'=>$criteria,
		));
		}
		return $this->findAll($criteria);
	}
	
	public function getFirm($firm)
	{
		 if(empty($firm)) return null; //return al if firm is empty
		 if(is_numeric($firm)) return $firm; //return fields if id is entered
		 $criteria=new CDbCriteria;
		 $criteria->select='id'; //select id field
		 $criteria->compare('name',$firm,true); //seach firm name. partialMatch is on
		 $fm=Firms::model()->find($criteria);
		 if(empty($fm)) return 0; //if firm not found - returns 0
		 return $fm->attributes['id']; //returns firm id
	}
}